//!@author denglibin
//!@date 2025/6/3 14:42
//!@desc sqlserver

use chrono::NaiveDateTime;
use futures_util::StreamExt;
use serde_json::Value;

use tiberius::{AuthMethod, Client, Config, Query};
use tokio::net::TcpStream;
use tokio_util::compat::{Compat, TokioAsyncWriteCompatExt};
use crate::dao::sqlserver::sql_server_dao;
use crate::sys::global::{to_global_result, GlobalResult};

const DATE_TIME_PATTERN: &str = "%Y-%m-%d %H:%M:%S";
pub async fn demo() -> std::result::Result<(), Box<dyn std::error::Error>> {
    // Using the builder method to construct the options.
    let mut config = Config::new();

    config.host("192.168.6.199");
    config.port(1433);

    // Using SQL Server authentication.
    config.authentication(AuthMethod::sql_server("sa", "Yiscn123456@"));
    config.database("suoyinshuju ");
    // on production, it is not a good idea to do this
    config.trust_cert();

    // Taking the address from the configuration, using async-std's
    // TcpStream to connect to the server.
    let tcp = TcpStream::connect(config.get_addr()).await?;

    // We'll disable the Nagle algorithm. Buffering is handled
    // internally with a `Sink`.
    tcp.set_nodelay(true)?;
    let tcp = tcp.compat_write();

    // Handling TLS, login and other details related to the SQL Server.
    // 连接数据库
    let mut client = Client::connect(config, tcp).await?;

    // Constructing a query object with one parameter annotated with `@P1`.
    // This requires us to bind a parameter that will then be used in
    // the statement.
    let mut select = Query::new("SELECT @P1");
    select.bind(-4i32);

    // A response to a query is a stream of data, that must be
    // polled to the end before querying again. Using streams allows
    // fetching data in an asynchronous manner, if needed.
    let stream = select.query(&mut client).await?;

    // In this case, we know we have only one query, returning one row
    // and one column, so calling `into_row` will consume the stream
    // and return us the first row of the first result.
    let row = stream.into_row().await?;

    assert_eq!(Some(-4i32), row.unwrap().get(0));
    client.close().await?;

    Ok(())
}

/// 获取客户端
/// @host 主机名
/// @port 端口
/// @database 数据库名
/// @user 用户名
/// @password 密码
/// @return 客户端实例
pub async fn get_client(
    host: &str,
    port: u16,
    database: &str,
    user: &str,
    password: &str,
) -> Result<Client<Compat<TcpStream>>, Box<dyn std::error::Error>> {
    // Using the builder method to construct the options.
    let mut config = Config::new();

    config.host(host);
    config.port(port);

    // Using SQL Server authentication.
    config.authentication(AuthMethod::sql_server(user, password));
    config.database(database);
    // on production, it is not a good idea to do this
    config.trust_cert();

    // Taking the address from the configuration, using async-std's
    // TcpStream to connect to the server.
    let tcp = TcpStream::connect(config.get_addr()).await?;

    // We'll disable the Nagle algorithm. Buffering is handled
    // internally with a `Sink`.
    tcp.set_nodelay(true)?;
    let tcp = tcp.compat_write();

    // Handling TLS, login and other details related to the SQL Server.
    // 连接数据库
    let client = Client::connect(config, tcp).await?;
    Ok(client)
}

///数据查询
/// @return 字段名 字段值
pub async fn select_list(
    sql: &str,
    client: &mut Client<Compat<TcpStream>>,
) -> Result<(Vec<String>, Vec<Vec<Value>>), Box<dyn std::error::Error>> {
    let query = Query::new(sql);
    let mut stream = query.query(client).await?;
    let mut columns = vec![];
    let mut row_list: Vec<Vec<Value>> = Vec::new();

    // 处理查询结果
    while let Some(item) = stream.next().await {

        let item = item?;
        if columns.is_empty() {
            let meta_data = item.as_metadata().unwrap();
            columns = meta_data
                .columns()
                .iter()
                .map(|column| column.name().to_string())
                .collect::<Vec<String>>();
        }

        if let Some(row) = item.as_row() {
            let mut row_data: Vec<Value> = Vec::new();
            //当前行数据
            for index in 0..columns.len() {
                let v: Value = row
                    .try_get::<i64, _>(index)
                    .map(Value::from)
                    .or_else(|_| row.try_get::<i64, _>(index).map(Value::from)) //数字类型
                    .or_else(|_| row.try_get::<i32, _>(index).map(Value::from)) //数字类型
                    .or_else(|_| row.try_get::<bool, _>(index).map(Value::from)) //bool类型
                    .or_else(|_| row.try_get::<&str, _>(index).map(Value::from)) //字符串类型
                    .or_else(|_| {
                        row.try_get::<NaiveDateTime, _>(index).map(|time| {
                            //时间类型
                            if let Some(t) = time {
                                Value::from(t.format(DATE_TIME_PATTERN).to_string())
                            } else {
                                Value::Null
                            }
                        })
                    })
                    .unwrap_or(Value::Null);
                row_data.push(v);
            }
            row_list.push(row_data);
        }


    }

    Ok((columns, row_list))
}


///获取表字段和类型
pub async fn get_table_columns(
    table: &str,
    sqlserver_client: &mut Client<Compat<TcpStream>>,
) -> GlobalResult<Vec<(String, String)>> {
    let sql = format!(
        "SELECT
    COLUMN_NAME,
    DATA_TYPE
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    TABLE_NAME = '{}'
ORDER BY
    ORDINAL_POSITION",
        table
    );

    //字段和数据
    let (_columns, rows) =
        to_global_result(sql_server_dao::select_list(&sql, sqlserver_client).await)?;
    let column_types = rows
        .iter()
        .map(|row| {
            (
                row.get(0).unwrap().as_str().unwrap().to_string(),
                row.get(1).unwrap().as_str().unwrap().to_string(),
            )
        })
        .collect::<Vec<(String, String)>>();
    Ok(column_types)
}

/// 关闭
pub async fn close(client: Client<Compat<TcpStream>>) -> Result<(), Box<dyn std::error::Error>> {
    client.close().await?;
    Ok(())
}
